from typing import Any, List, Dict, Optional, Tuple
from langchain.tools import BaseTool, tool
import json
from .database_manager import DatabaseManager
import mysql.connector
from pydantic import Field
from decimal import Decimal
from datetime import datetime
import random


class DatabaseTool(BaseTool):
    name = "数据库工具"
    description = "用于查询和操作数据库的工具"
    db_manager: DatabaseManager = None
    connection: Any = Field(default=None)
    cursor: Any = Field(default=None)

    def __init__(self, **data):
        super().__init__(**data)
        self.db_manager = DatabaseManager()
        self.connection = mysql.connector.connect(
            host="localhost",
            user="root",
            password="20539",
            database="smart_supermarket",
            autocommit=False  # 禁用自动提交
        )
        self.cursor = self.connection.cursor(dictionary=True)

    def _ensure_connection(self):
        """确保数据库连接有效"""
        try:
            self.connection.ping(reconnect=True, attempts=3, delay=5)
        except mysql.connector.Error as err:
            # 重新连接
            self.connection = mysql.connector.connect(
                host="localhost",
                user="root",
                password="20539",
                database="smart_supermarket",
                autocommit=False
            )
            self.cursor = self.connection.cursor(dictionary=True)

    def _begin_transaction(self):
        """开始新的事务"""
        self._ensure_connection()
        try:
            # 如果有未完成的事务，先回滚
            self.connection.rollback()
            self.connection.start_transaction()
        except mysql.connector.Error as err:
            print(f"开始事务时出错: {err}")
            raise

    def check_stock(self, product_id: int) -> Dict[str, Any]:
        """检查商品库存"""
        query = "SELECT * FROM products WHERE id = %s"
        self.cursor.execute(query, (product_id,))
        return self.cursor.fetchone()

    def update_stock(self, product_id: int, quantity: int, change_type: str, operator: str = "系统") -> bool:
        """更新库存"""
        try:
            self._begin_transaction()

            # 获取当前库存
            current_stock = self.check_stock(product_id)
            if not current_stock:
                raise Exception("商品不存在")

            # 计算新库存
            new_stock = current_stock['stock']
            if change_type == 'In':
                new_stock += quantity
            elif change_type == 'Out':
                if new_stock < quantity:
                    raise Exception("库存不足")
                new_stock -= quantity

            # 更新products表的库存
            update_query = "UPDATE products SET stock = %s WHERE id = %s"
            self.cursor.execute(update_query, (new_stock, product_id))

            # 记录库存变动到inventory表
            inventory_query = """
            INSERT INTO inventory 
            (product_id, change_type, change_quantity, operator, change_time) 
            VALUES (%s, %s, %s, %s, %s)
            """
            current_time = datetime.now()
            self.cursor.execute(inventory_query, (
                product_id,
                change_type,
                quantity,
                operator,
                current_time
            ))

            # 提交事务
            self.connection.commit()
            return True

        except Exception as e:
            # 发生错误时回滚
            try:
                self.connection.rollback()
            except:
                pass
            print(f"更新库存时出错: {str(e)}")
            raise e

    def process_payment(self, product_id: int, quantity: int, 
                       payment_method: str, coupon_id: Optional[int] = None) -> Tuple[bool, float]:
        """处理支付"""
        try:
            self._begin_transaction()

            # 获取商品信息
            product = self.check_stock(product_id)
            if not product:
                raise Exception("商品不存在")

            # 计算总金额
            total_amount = Decimal(str(product['price'])) * Decimal(str(quantity))

            # 如果有优惠券，应用优惠
            if coupon_id:
                coupon_query = "SELECT * FROM coupons WHERE id = %s AND status = 'active'"
                self.cursor.execute(coupon_query, (coupon_id,))
                coupon = self.cursor.fetchone()
                
                if coupon:
                    if coupon['type'] == 'percentage':
                        discount = Decimal(str(coupon['discount_value']))
                        total_amount *= (Decimal('1.0') - discount)
                    elif coupon['type'] == 'dollar' and total_amount >= Decimal(str(coupon['min_purchase_amount'])):
                        total_amount -= Decimal(str(coupon['discount_value']))

            # 创建销售记录
            current_time = datetime.now()
            sales_query = """
            INSERT INTO sales 
            (product_id, quantity, total_amount, payment_method, coupon_id, sale_date, updated_at) 
            VALUES (%s, %s, %s, %s, %s, %s, %s)
            """
            self.cursor.execute(sales_query, (
                product_id,
                quantity,
                float(total_amount),
                payment_method,
                coupon_id,
                current_time,
                current_time
            ))

            # 提交事务
            self.connection.commit()
            return True, float(total_amount)

        except Exception as e:
            # 发生错误时回滚
            try:
                self.connection.rollback()
            except:
                pass
            print(f"处理支付时出错: {str(e)}")
            raise e

    def get_low_stock_products(self, threshold: int = 10) -> List[Dict[str, Any]]:
        """获取库存低于阈值的商品"""
        query = "SELECT * FROM products WHERE stock <= %s"
        self.cursor.execute(query, (threshold,))
        return self.cursor.fetchall()

    def get_inventory_history(self, product_id: Optional[int] = None, 
                            start_date: Optional[str] = None,
                            end_date: Optional[str] = None) -> List[Dict[str, Any]]:
        """获取库存变动历史"""
        query = """
        SELECT i.*, p.name as product_name, p.price
        FROM inventory i
        JOIN products p ON i.product_id = p.id
        WHERE 1=1
        """
        params = []

        if product_id:
            query += " AND i.product_id = %s"
            params.append(product_id)
        if start_date:
            query += " AND i.change_time >= %s"
            params.append(start_date)
        if end_date:
            query += " AND i.change_time <= %s"
            params.append(end_date)

        query += " ORDER BY i.change_time DESC"
        
        self.cursor.execute(query, tuple(params))
        return self.cursor.fetchall()

    def _run(self, query: str) -> Any:
        """实现BaseTool的抽象方法"""
        try:
            results = self.db_manager.execute_query(query)
            return json.dumps(results, ensure_ascii=False)
        except Exception as e:
            return f"查询执行错误: {str(e)}"

    def _arun(self, query: str) -> Any:
        """实现BaseTool的异步抽象方法"""
        raise NotImplementedError("暂不支持异步操作")

    @tool("查询商品信息")
    def tool(self, scene: str = "", keyword: str = "", query_type: str = "推荐", limit: int = 10):
        """
        查询商品信息的工具
        参数:
            scene: 使用场景描述
            keyword: 搜索关键词
            query_type: 查询类型，可以是"推荐"、"热销"、"新品"等
            limit: 返回结果数量限制
        """
        try:
            # 场景映射
            scene_category_mapping = {
                "学习": ["电子产品", "文具", "饮品", "零食"],
                "健身": ["运动用品", "健身器材", "蛋白质饮品", "运动饮料", "水果", "蔬菜", "肉类", "乳制品", "运动服装"],
                "做饭": ["厨具", "食材", "烘焙", "调味品", "厨房电器"],
                "清洁": ["清洁用品", "家居用品", "洗护用品", "消毒用品"],
                "厨房": ["厨具", "家居用品", "厨房电器", "储物用品"],
                "烹饪": ["厨具", "食材", "烘焙", "调味品", "厨房电器"],
                "运动": ["运动用品", "健身器材", "运动饮料", "运动服装", "运动护具"],
                "办公": ["电子产品", "文具", "办公设备", "饮品", "零食"],
                "购物": ["水果", "蔬菜", "乳制品", "肉类", "饮品", "零食", "日用品"],
                "饮食": ["水果", "蔬菜", "乳制品", "肉类", "饮品", "零食", "烘焙", "调味品"],
                "瑜伽": ["瑜伽垫", "运动服装", "运动饮料", "运动护具"],
                "跑步": ["运动鞋", "运动服装", "运动饮料", "运动护具"],
                "游泳": ["泳衣", "泳镜", "运动饮料", "运动护具"],
                "球类运动": ["球类用品", "运动服装", "运动饮料", "运动护具"],
                "户外活动": ["户外装备", "运动服装", "运动饮料", "零食"],
                "居家健身": ["健身器材", "运动服装", "运动饮料", "运动护具"],
                "减肥": ["水果", "蔬菜", "运动用品", "健身器材"],
                "增肌": ["肉类", "乳制品", "运动用品", "健身器材"],
                "瑜伽": ["运动用品"],
                "跑步": ["运动用品", "饮品"],
                "游泳": ["运动用品"],
                "球类运动": ["运动用品"],
                "户外活动": ["运动用品", "饮品", "零食"],
                "居家健身": ["健身器材", "运动用品"]
            }
            
            # 场景关键词映射
            scene_keywords = {
                "健身": ["健身", "锻炼", "运动", "健康", "肌肉", "力量", "有氧", "无氧", "增肌", "减脂"],
                "学习": ["学习", "读书", "考试", "上课", "笔记", "作业", "论文", "研究"],
                "做饭": ["做饭", "烹饪", "厨房", "煮", "炒", "蒸", "煎", "炸", "烤", "菜谱"],
                "清洁": ["清洁", "打扫", "卫生", "洗涤", "擦拭", "消毒", "除尘", "除菌"],
                "运动": ["运动", "跑步", "游泳", "篮球", "足球", "羽毛球", "乒乓球", "健身", "瑜伽"],
                "办公": ["办公", "工作", "会议", "文档", "报表", "演示", "项目", "计划"],
                "购物": ["购物", "买东西", "逛街", "选购", "比价", "促销", "打折", "优惠"],
                "饮食": ["饮食", "吃饭", "餐饮", "美食", "营养", "健康饮食", "食谱", "菜品"]
            }
            
            # 场景特定商品推荐
            scene_specific_products = {
                "健身": [
                    {"category": "运动用品", "keywords": ["哑铃", "杠铃", "健身器材", "跑步机", "瑜伽垫", "拉力器", "弹力带"]},
                    {"category": "健身器材", "keywords": ["多功能健身器", "仰卧板", "健腹轮", "臂力器", "握力器"]},
                    {"category": "运动服装", "keywords": ["运动服", "运动裤", "运动内衣", "运动袜"]},
                    {"category": "运动护具", "keywords": ["护腕", "护膝", "护踝", "运动护带"]},
                    {"category": "运动饮料", "keywords": ["能量饮料", "运动饮料", "维生素饮料"]},
                    {"category": "蛋白质饮品", "keywords": ["蛋白粉", "增肌粉", "氨基酸"]},
                    {"category": "水果", "keywords": ["香蕉", "苹果", "橙子", "火龙果"]},
                    {"category": "肉类", "keywords": ["鸡胸肉", "牛肉", "三文鱼"]},
                    {"category": "乳制品", "keywords": ["酸奶", "牛奶", "乳清蛋白"]}
                ],
                "瑜伽": [
                    {"category": "运动用品", "keywords": ["瑜伽垫", "瑜伽砖", "瑜伽球", "瑜伽带"]},
                    {"category": "运动服装", "keywords": ["瑜伽服", "运动内衣", "瑜伽裤"]},
                    {"category": "运动护具", "keywords": ["防滑袜", "护腕", "护膝"]}
                ],
                "跑步": [
                    {"category": "运动用品", "keywords": ["跑步机", "计步器", "运动手环"]},
                    {"category": "运动服装", "keywords": ["跑步鞋", "运动服", "运动裤", "运动袜"]},
                    {"category": "运动护具", "keywords": ["护膝", "护踝", "运动护带"]},
                    {"category": "运动饮料", "keywords": ["能量饮料", "运动饮料", "维生素饮料"]}
                ],
                "游泳": [
                    {"category": "运动用品", "keywords": ["泳镜", "泳帽", "游泳圈", "游泳板"]},
                    {"category": "运动服装", "keywords": ["泳衣", "泳裤", "防晒衣"]},
                    {"category": "运动护具", "keywords": ["耳塞", "鼻夹", "防水包"]}
                ],
                "球类运动": [
                    {"category": "运动用品", "keywords": ["篮球", "足球", "羽毛球", "乒乓球", "网球"]},
                    {"category": "运动服装", "keywords": ["球鞋", "运动服", "运动裤", "运动袜"]},
                    {"category": "运动护具", "keywords": ["护腕", "护膝", "护踝", "运动护带"]}
                ],
                "户外活动": [
                    {"category": "户外装备", "keywords": ["帐篷", "睡袋", "登山杖", "户外背包"]},
                    {"category": "运动服装", "keywords": ["户外服装", "登山鞋", "速干衣", "防晒衣"]},
                    {"category": "运动护具", "keywords": ["护膝", "护踝", "防晒用品"]},
                    {"category": "零食", "keywords": ["能量棒", "压缩饼干", "坚果", "巧克力"]}
                ],
                "居家健身": [
                    {"category": "健身器材", "keywords": ["多功能健身器", "跑步机", "动感单车", "哑铃", "瑜伽垫"]},
                    {"category": "运动服装", "keywords": ["运动服", "运动裤", "运动内衣"]},
                    {"category": "运动护具", "keywords": ["护腕", "护膝", "运动护带"]},
                    {"category": "运动饮料", "keywords": ["能量饮料", "运动饮料", "维生素饮料"]}
                ]
            }

            # 检测场景
            detected_scene = scene
            if not detected_scene and keyword:
                # 从关键词中检测场景
                for s, kws in scene_keywords.items():
                    for kw in kws:
                        if kw in keyword.lower():
                            detected_scene = s
                            break
                    if detected_scene:
                        break
            
            # 获取场景相关的类别
            categories = []
            if detected_scene in scene_category_mapping:
                categories = scene_category_mapping[detected_scene]
            
            # 构建查询条件
            search_conditions = []
            search_params = []
            
            # 基础查询
            base_sql = """
            SELECT p.*, 
                CASE 
                    WHEN p.category_name IN ({}) THEN 5
                    WHEN p.product_description LIKE %s THEN 4
                    WHEN p.category_description LIKE %s THEN 3
                    WHEN p.name LIKE %s OR p.brand LIKE %s THEN 2
                    ELSE 0
                END as relevance_score,
                (SELECT COUNT(*) FROM sales s WHERE s.product_id = p.id) as total_sales
            FROM products p
            WHERE 1=1
            """
            
            # 添加关键词搜索条件
            search_term = f"%{keyword}%" if keyword else "%%"
            search_params.extend([search_term, search_term, search_term, search_term])
            
            # 添加类别条件
            if categories:
                placeholders = ", ".join(["%s"] * len(categories))
                category_condition = f"AND (p.category_name IN ({placeholders}))"
                search_conditions.append(category_condition)
                search_params.extend(categories)
            
            # 添加场景特定商品条件
            if detected_scene in scene_specific_products:
                scene_products = scene_specific_products[detected_scene]
                for product_group in scene_products:
                    category = product_group["category"]
                    keywords = product_group["keywords"]
                    if keywords:
                        keyword_conditions = []
                        for kw in keywords:
                            keyword_conditions.append("p.name LIKE %s")
                            search_params.append(f"%{kw}%")
                        keyword_sql = f"AND (p.category_name = %s AND ({' OR '.join(keyword_conditions)}))"
                        search_conditions.append(keyword_sql)
                        search_params.append(category)
            
            # 构建完整SQL
            categories_placeholder = ", ".join(["%s"] * max(1, len(categories)))
            sql = base_sql.format(categories_placeholder)
            
            if search_conditions:
                sql += " " + " ".join(search_conditions)
            
            # 添加排序和限制
            if query_type == "热销":
                sql += " ORDER BY total_sales DESC"
            elif query_type == "新品":
                sql += " ORDER BY p.created_at DESC"
            else:  # 默认按相关性排序
                sql += " ORDER BY relevance_score DESC, total_sales DESC"
            
            sql += f" LIMIT {limit}"
            
            # 执行查询
            self._ensure_connection()
            self.cursor.execute(sql, tuple(search_params))
            products = self.cursor.fetchall()
            
            # 处理结果
            result = {
                "success": True,
                "message": f"找到 {len(products)} 个相关商品",
                "data": {
                    "products": products,
                    "total_found": len(products),
                    "scene": detected_scene,
                    "categories": categories
                }
            }
            
            return json.dumps(result, ensure_ascii=False, default=str)
            
        except Exception as e:
            error_message = f"查询商品信息时出错: {str(e)}"
            print(error_message)
            return json.dumps({
                "success": False,
                "message": error_message,
                "data": None
            }, ensure_ascii=False)

    def get_products_by_category(self, category: str) -> List[Dict[str, Any]]:
        """根据类别获取商品"""
        try:
            query = "SELECT * FROM products WHERE category_name = %s"
            self._ensure_connection()
            self.cursor.execute(query, (category,))
            return self.cursor.fetchall()
        except Exception as e:
            print(f"根据类别获取商品时出错: {str(e)}")
            return []

    def get_products_by_price_range(self, min_price: float, max_price: float) -> List[Dict[str, Any]]:
        """根据价格范围获取商品"""
        try:
            query = "SELECT * FROM products WHERE price BETWEEN %s AND %s"
            self._ensure_connection()
            self.cursor.execute(query, (min_price, max_price))
            return self.cursor.fetchall()
        except Exception as e:
            print(f"根据价格范围获取商品时出错: {str(e)}")
            return []

    def search_products(self, keyword: str) -> List[Dict[str, Any]]:
        """搜索商品"""
        try:
            # 检查是否是场景关键词
            scene_keywords = {
                "健身": ["健身", "锻炼", "运动", "健康", "肌肉", "力量", "有氧", "无氧", "增肌", "减脂"],
                "学习": ["学习", "读书", "考试", "上课", "笔记", "作业", "论文", "研究"],
                "做饭": ["做饭", "烹饪", "厨房", "煮", "炒", "蒸", "煎", "炸", "烤", "菜谱"],
                "清洁": ["清洁", "打扫", "卫生", "洗涤", "擦拭", "消毒", "除尘", "除菌"],
                "运动": ["运动", "跑步", "游泳", "篮球", "足球", "羽毛球", "乒乓球", "健身", "瑜伽"],
                "办公": ["办公", "工作", "会议", "文档", "报表", "演示", "项目", "计划"],
                "购物": ["购物", "买东西", "逛街", "选购", "比价", "促销", "打折", "优惠"],
                "饮食": ["饮食", "吃饭", "餐饮", "美食", "营养", "健康饮食", "食谱", "菜品"]
            }
            
            scene_category_mapping = {
                "学习": ["电子产品", "文具"],
                "健身": ["运动用品", "健身器材", "水果", "蔬菜", "肉类"],
                "做饭": ["厨具", "食材", "烘焙"],
                "清洁": ["清洁用品", "家居用品"],
                "厨房": ["厨具", "家居用品"],
                "烹饪": ["厨具", "食材", "烘焙"],
                "运动": ["运动用品", "健身器材"],
                "办公": ["电子产品", "文具"],
                "购物": ["水果", "蔬菜", "乳制品", "肉类", "饮品", "零食"],
                "饮食": ["水果", "蔬菜", "乳制品", "肉类", "饮品", "零食", "烘焙"]
            }
            
            # 检测场景
            detected_scene = None
            keyword_lower = keyword.lower()
            for scene, keywords in scene_keywords.items():
                for kw in keywords:
                    if kw in keyword_lower:
                        detected_scene = scene
                        break
                if detected_scene:
                    break
            
            if detected_scene:
                # 如果是场景关键词，获取相关类别的商品
                categories = scene_category_mapping.get(detected_scene, [])
                if categories:
                    all_products = []
                    for category in categories:
                        category_products = self.get_products_by_category(category)
                        all_products.extend(category_products)
                    
                    # 按相关性排序
                    all_products.sort(key=lambda p: 
                        (keyword_lower in p.get("product_description", "").lower(),
                         keyword_lower in p.get("name", "").lower(),
                         keyword_lower in p.get("category_description", "").lower()),
                        reverse=True
                    )
                    
                    return all_products
            
            # 常规搜索
            query = """
            SELECT * FROM products 
            WHERE name LIKE %s 
               OR brand LIKE %s 
               OR category_name LIKE %s 
               OR product_description LIKE %s
            """
            search_term = f"%{keyword}%"
            self._ensure_connection()
            self.cursor.execute(query, (search_term, search_term, search_term, search_term))
            return self.cursor.fetchall()
        except Exception as e:
            print(f"搜索商品时出错: {str(e)}")
            return []

    def get_popular_products(self, limit: int = 5) -> List[Dict[str, Any]]:
        """获取热门商品"""
        try:
            query = """
            SELECT p.*, COUNT(s.id) as sales_count 
            FROM products p 
            LEFT JOIN sales s ON p.id = s.product_id 
            GROUP BY p.id 
            ORDER BY sales_count DESC 
            LIMIT %s
            """
            self._ensure_connection()
            self.cursor.execute(query, (limit,))
            return self.cursor.fetchall()
        except Exception as e:
            print(f"获取热门商品时出错: {str(e)}")
            return []

    def get_product_by_id(self, product_id: int) -> Optional[Dict[str, Any]]:
        """根据ID获取商品"""
        try:
            query = "SELECT * FROM products WHERE id = %s"
            self._ensure_connection()
            self.cursor.execute(query, (product_id,))
            return self.cursor.fetchone()
        except Exception as e:
            print(f"根据ID获取商品时出错: {str(e)}")
            return None

    def get_product_by_name(self, name: str) -> Optional[List[Dict[str, Any]]]:
        """根据商品名称查找商品"""
        query = "SELECT * FROM products WHERE name LIKE %s"
        self.cursor.execute(query, (f"%{name}%",))
        return self.cursor.fetchall()

    def handle_inventory_query(self, query_params: Dict[str, str]) -> Dict[str, Any]:
        """处理库存查询"""
        try:
            product = query_params.get("product", "")
            category = query_params.get("category", "")
            scene = query_params.get("scene", "")
            
            if product:
                # 按商品名称查询
                products = self.search_products(product)
                if products:
                    result = []
                    for p in products:
                        result.append({
                            "id": p["id"],
                            "name": p["name"],
                            "price": p["price"],
                            "stock": p["stock"],
                            "category": p["category_name"]
                        })
                    return {
                        "success": True,
                        "message": f"找到 {len(result)} 个相关商品的库存信息",
                        "data": {
                            "products": result
                        }
                    }
                else:
                    return {
                        "success": False,
                        "message": f"未找到商品 '{product}' 的库存信息",
                        "data": None
                    }
            elif category:
                # 按类别查询
                products = self.get_products_by_category(category)
                if products:
                    result = []
                    for p in products:
                        result.append({
                            "id": p["id"],
                            "name": p["name"],
                            "price": p["price"],
                            "stock": p["stock"],
                            "category": p["category_name"]
                        })
                    return {
                        "success": True,
                        "message": f"找到 {len(result)} 个 '{category}' 类别商品的库存信息",
                        "data": {
                            "products": result
                        }
                    }
                else:
                    return {
                        "success": False,
                        "message": f"未找到 '{category}' 类别商品的库存信息",
                        "data": None
                    }
            elif scene:
                # 按场景查询，使用tool方法
                result = json.loads(self.tool(query_type="推荐", scene=scene, limit=10))
                if result["success"] and result["data"]["products"]:
                    return {
                        "success": True,
                        "message": f"找到 {len(result['data']['products'])} 个适合 '{scene}' 场景的商品库存信息",
                        "data": {
                            "products": result["data"]["products"]
                        }
                    }
                else:
                    return {
                        "success": False,
                        "message": f"未找到适合 '{scene}' 场景的商品库存信息",
                        "data": None
                    }
            else:
                # 获取库存低的商品
                products = self.get_low_stock_products(10)
                if products:
                    result = []
                    for p in products:
                        result.append({
                            "id": p["id"],
                            "name": p["name"],
                            "price": p["price"],
                            "stock": p["stock"],
                            "category": p["category_name"]
                        })
                    return {
                        "success": True,
                        "message": f"以下 {len(result)} 个商品库存较低",
                        "data": {
                            "products": result
                        }
                    }
                else:
                    # 如果没有库存低的商品，返回热门商品
                    products = self.get_popular_products(5)
                    result = []
                    for p in products:
                        result.append({
                            "id": p["id"],
                            "name": p["name"],
                            "price": p["price"],
                            "stock": p["stock"],
                            "category": p["category_name"]
                        })
                    return {
                        "success": True,
                        "message": "所有商品库存充足，以下是热门商品",
                        "data": {
                            "products": result
                        }
                    }
        except Exception as e:
            print(f"处理库存查询时出错: {str(e)}")
            return {
                "success": False,
                "message": f"处理库存查询时出错: {str(e)}",
                "data": None
            }
